﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class CauHoiDAO
    {
        #region lay tat ca cau hoi
        public static List<CauHoiDTO> getAll()
        {
            SqlConnection connect = null;
            List<CauHoiDTO> list = new List<CauHoiDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from cauhoi where status=1";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CauHoiDTO ch = new CauHoiDTO();
                    ch.Id = reader.GetInt32(0);
                    ch.NguoiSoHuu = reader.GetInt32(1);
                    ch.DiaDiemId = reader.GetInt32(2);
                    ch.MucDo = reader.GetInt32(3);
                    ch.NoiDung = reader.GetString(4); 
                    ch.DapAnA = reader.GetString(5);
                    ch.DapAnB = reader.GetString(6);
                    ch.DapAnC = reader.GetString(7);
                    ch.DapAnD = reader.GetString(8);
                    ch.DapAnE = reader.GetString(9);
                    ch.ADung = reader.GetInt32(10);
                    ch.BDung = reader.GetInt32(11);
                    ch.CDung = reader.GetInt32(12);
                    ch.DDung = reader.GetInt32(13);
                    ch.EDung = reader.GetInt32(14);
                    ch.Status = reader.GetInt32(15);
                    list.Add(ch);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region Lay cau hoi theo ma
        public static CauHoiDTO getQuestionById(int maCauHoi)
        {
            SqlConnection connect = null;
            CauHoiDTO ch = new CauHoiDTO();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from cauhoi where status=1 and Id=" + maCauHoi;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    ch.Id = reader.GetInt32(0);
                    ch.NguoiSoHuu = reader.GetInt32(1);
                    ch.DiaDiemId = reader.GetInt32(2);
                    ch.MucDo = reader.GetInt32(3);
                    ch.NoiDung = reader.GetString(4);
                    ch.DapAnA = reader.GetString(5);
                    ch.DapAnB = reader.GetString(6);
                    ch.DapAnC = reader.GetString(7);
                    ch.DapAnD = reader.GetString(8);
                    ch.DapAnE = reader.GetString(9);
                    ch.ADung = reader.GetInt32(10);
                    ch.BDung = reader.GetInt32(11);
                    ch.CDung = reader.GetInt32(12);
                    ch.DDung = reader.GetInt32(13);
                    ch.EDung = reader.GetInt32(14);
                    ch.Status = reader.GetInt32(15);
                }
            }
            catch (Exception ex)
            {
                ch = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return ch;
        }
        #endregion

        #region Lay cau hoi theo dia diem
        public static List<CauHoiDTO> getQuestionsByDiaDiemId(int diaDiemId)
        {
            SqlConnection connect = null;
            List<CauHoiDTO> list = new List<CauHoiDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from cauhoi where status=1 and diadiemid=" + diaDiemId;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CauHoiDTO ch = new CauHoiDTO();
                    ch.Id = reader.GetInt32(0);
                    ch.NguoiSoHuu = reader.GetInt32(1);
                    ch.DiaDiemId = reader.GetInt32(2);
                    ch.MucDo = reader.GetInt32(3);
                    ch.NoiDung = reader.GetString(4);
                    ch.DapAnA = reader.GetString(5);
                    ch.DapAnB = reader.GetString(6);
                    ch.DapAnC = reader.GetString(7);
                    ch.DapAnD = reader.GetString(8);
                    ch.DapAnE = reader.GetString(9);
                    ch.ADung = reader.GetInt32(10);
                    ch.BDung = reader.GetInt32(11);
                    ch.CDung = reader.GetInt32(12);
                    ch.DDung = reader.GetInt32(13);
                    ch.EDung = reader.GetInt32(14);
                    ch.Status = reader.GetInt32(15);
                    list.Add(ch);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion
    }
}
